﻿CREATE TABLE [cook].[Users] (
  [ID] bigint IDENTITY(1, 1) NOT NULL,
  [UserName] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [PasswordHash] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Email] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  CONSTRAINT [Users_pk] PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [Users_uq] UNIQUE ([UserName])
)
ON [PRIMARY]
GO

CREATE TABLE [cook].[Recipes] (
  [ID] bigint IDENTITY(1, 1) NOT NULL,
  [Title] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Products] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Description] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Notes] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [Date] datetime DEFAULT getdate() NOT NULL,
  [ExternalId] bigint NULL,
  [ExternalUrl] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [CurrentRating] float DEFAULT 0 NOT NULL,
  [SmallImageUrl] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  CONSTRAINT [Recipes_pk] PRIMARY KEY CLUSTERED ([ID])
)
ON [PRIMARY]
GO

CREATE TABLE [cook].[RecipeImages] (
  [ID] bigint IDENTITY(1, 1) NOT NULL,
  [RecipeId] bigint NOT NULL,
  [ImageUrl] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Description] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  CONSTRAINT [RecipeImages_pk] PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [RecipeImages_Recipes_fk] FOREIGN KEY ([RecipeId]) 
  REFERENCES [cook].[Recipes] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [RecipeImages_idx] ON [cook].[RecipeImages]
  ([RecipeId])
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE TABLE [cook].[UserRecipeRating] (
  [ID] bigint IDENTITY(1, 1) NOT NULL,
  [UserId] bigint NOT NULL,
  [RecipeId] bigint NOT NULL,
  [Rating] float NOT NULL,
  CONSTRAINT [UserRecipeRating_pk] PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [UserRecipeRating_uq] UNIQUE ([UserId], [RecipeId]),
  CONSTRAINT [UserRecipeRating_Recipes_fk] FOREIGN KEY ([RecipeId]) 
  REFERENCES [cook].[Recipes] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE,
  CONSTRAINT [UserRecipeRating_Users_fk] FOREIGN KEY ([UserId]) 
  REFERENCES [cook].[Users] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE
)
ON [PRIMARY]
GO

CREATE TABLE [cook].[RecipeComments] (
  [ID] bigint IDENTITY(1, 1) NOT NULL,
  [RecipeId] bigint NOT NULL,
  [UserId] bigint NOT NULL,
  [Comment] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Date] datetime DEFAULT getdate() NOT NULL,
  CONSTRAINT [RecipeComments_pk] PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [RecipeComments_Recipes_fk] FOREIGN KEY ([RecipeId]) 
  REFERENCES [cook].[Recipes] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE,
  CONSTRAINT [RecipeComments_Users_fk] FOREIGN KEY ([UserId]) 
  REFERENCES [cook].[Users] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [RecipeComments_idx] ON [cook].[RecipeComments]
  ([RecipeId])
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE TABLE [cook].[FavoriteRecipes] (
  [ID] bigint IDENTITY(1, 1) NOT NULL,
  [RecipeId] bigint NOT NULL,
  [UserId] bigint NOT NULL,
  CONSTRAINT [FavoriteRecipes_pk] PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [FavoriteRecipes_uq] UNIQUE ([RecipeId], [UserId]),
  CONSTRAINT [FavoriteRecipes_Recipes_fk] FOREIGN KEY ([RecipeId]) 
  REFERENCES [cook].[Recipes] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE,
  CONSTRAINT [FavoriteRecipes_Users_fk] FOREIGN KEY ([UserId]) 
  REFERENCES [cook].[Users] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE CASCADE
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [FavoriteRecipes_idx] ON [cook].[FavoriteRecipes]
  ([UserId])
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
